﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using Microsoft.ApplicationBlocks.Data;
using System.Data.SqlClient;

namespace WCS.DNN.WCSServices
{
    public class AdminDataService
    {
        #region Private variables
        //private const string CONNECTION = "SiteSqlServerWcs";
        //private const string CONNECTION = "WCSPredict";
        private const string CONNECTION = "SiteSqlServer";
        private static string ConnectionString;
        private static AdminDataService objProvider = null;
        #endregion;

        #region Initialize
        static AdminDataService()
        {
            ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings[CONNECTION].ConnectionString;
            CreateProvider();
        }

        private static void CreateProvider()
        {
            objProvider = new AdminDataService();
        }

        public static AdminDataService Instance()
        {
            return objProvider;
        }
        #endregion;


        #region Data Access Implemetations

        public List<DTO_SCT_Form> GetAllForms()
        {
            try
            {
                DataTable sections = SqlHelper.ExecuteDataset(ConnectionString, "SCT_GetAllForms").Tables[0];

                List<DTO_SCT_Form> formArray = new List<DTO_SCT_Form>();
                for (int i = 0; i < sections.Rows.Count; i++)
                {
                    formArray.Add(AdminDataTranforms.TranformFormToDTO(sections.Rows[i]));
                }
                return formArray;
            }
            catch (Exception ex)
            {
                throw new Exception("Error processing GetAllForms:" + ex.Message);
            }
        }

        public List<DTO_SCT_Form> GetAllFormsAll()
        {
            try
            {
                DataTable sections = SqlHelper.ExecuteDataset(ConnectionString, "SCT_GetAllFormsAll").Tables[0];

                List<DTO_SCT_Form> formArray = new List<DTO_SCT_Form>();
                for (int i = 0; i < sections.Rows.Count; i++)
                {
                    formArray.Add(AdminDataTranforms.TranformFormToDTO(sections.Rows[i]));
                }
                return formArray;
            }
            catch (Exception ex)
            {
                throw new Exception("Error processing GetAllForms:" + ex.Message);
            }
        }

        public List<DTO_SCT_Form> GetFormbyId(int formId)
        {
            try
            {
                DataTable forms = SqlHelper.ExecuteDataset(ConnectionString, "SCT_GetFormById", formId).Tables[0];

                // Even though there is alway just one form, it's more convinient to construct the array here
                List<DTO_SCT_Form> formsArray = new List<DTO_SCT_Form>();
                for (int i = 0; i < forms.Rows.Count; i++)
                {
                    formsArray.Add(AdminDataTranforms.TranformFormToDTO(forms.Rows[i]));
                }
                return formsArray;
            }
            catch (Exception ex)
            {
                throw new Exception("Error processing GetFormbyId:" + ex.Message);
            }
        }

        public List<DTO_SCT_FormPage> GetFormPagesbyForm(int formId)
        {
            try
            {
                DataTable pages = SqlHelper.ExecuteDataset(ConnectionString, "SCT_GetFormPagesByForm", formId).Tables[0];

                List<DTO_SCT_FormPage> formPages = new List<DTO_SCT_FormPage>();
                for (int i = 0; i < pages.Rows.Count; i++)
                {
                   
                    formPages.Add(AdminDataTranforms.TranformFormPageToDTO(pages.Rows[i]));
                }

                return formPages;
            }
            catch (Exception ex)
            {
                throw new Exception("Error processing GetFormPagebyForm:" + ex.Message);
            }
        }

        public List<DTO_SCT_FormSection> GetFormSectionsForPage(int pageId)
        {

            try
            {
                DataTable sections = SqlHelper.ExecuteDataset(ConnectionString, "SCT_GetFormSectionsForPage", pageId).Tables[0];

                List<DTO_SCT_FormSection> formSectionArray = new List<DTO_SCT_FormSection>();
                for (int i = 0; i < sections.Rows.Count; i++)
                {
                   
                    formSectionArray.Add(AdminDataTranforms.TranformFormSectionToDTO(sections.Rows[i]));
                }
                return formSectionArray;
            }
            catch (Exception ex)
            {
                throw new Exception("Error processing GetFormSectionsForPage:" + ex.Message);
            }
        }

        public List<DTO_SCT_FormSectionField> GetFormSectionsFieldsForSection(int sectionId)
        {

            try
            {
                DataTable sections = SqlHelper.ExecuteDataset(ConnectionString, "SCT_GetFormSectionFieldsForSection", sectionId).Tables[0];

                List<DTO_SCT_FormSectionField> formSectionArray = new List<DTO_SCT_FormSectionField>();
                for (int i = 0; i < sections.Rows.Count; i++)
                {
                    


                    formSectionArray.Add(AdminDataTranforms.TranformFormSectionFieldToDTO(sections.Rows[i]));
                }
                return formSectionArray;
            }
            catch (Exception ex)
            {
                throw new Exception("Error processing GetFormSectionsFieldsForSection:" + ex.Message);
            }
        }

        public List<DTO_SCT_FormType> GetFormTypes()
        {

            try
            {
                DataTable sections = SqlHelper.ExecuteDataset(ConnectionString, "SCT_GetFormTypes").Tables[0];

                List<DTO_SCT_FormType> formTypesArray = new List<DTO_SCT_FormType>();
                for (int i = 0; i < sections.Rows.Count; i++)
                {
                   

                    formTypesArray.Add(AdminDataTranforms.TranformFormTypeToDTO(sections.Rows[i]));
                }
                return formTypesArray;
            }
            catch (Exception ex)
            {
                throw new Exception("Error processing GetFormTypes:" + ex.Message);
            }
        }

        public List<DTO_SCT_DataCategory> GetDataCategories()
        {

            try
            {
                DataTable sections = SqlHelper.ExecuteDataset(ConnectionString, "SCT_GetDataCategories").Tables[0];

                List<DTO_SCT_DataCategory> formTypesArray = new List<DTO_SCT_DataCategory>();
                for (int i = 0; i < sections.Rows.Count; i++)
                {
                    

                    formTypesArray.Add(AdminDataTranforms.TranformDataCategoryToDTO(sections.Rows[i]));
                }
                return formTypesArray;
            }
            catch (Exception ex)
            {
                throw new Exception("Error processing GetDataCategories:" + ex.Message);
            }
        }

        public List<DTO_SCT_DataField> GetDataFields()
        {

            try
            {
                DataTable sections = SqlHelper.ExecuteDataset(ConnectionString, "SCT_GetAllDataFields").Tables[0];

                List<DTO_SCT_DataField> dataFieldsArray = new List<DTO_SCT_DataField>();
                for (int i = 0; i < sections.Rows.Count; i++)
                {
                   
                    dataFieldsArray.Add(AdminDataTranforms.TranformDataFieldToDTO(sections.Rows[i]));
                }
                return dataFieldsArray;
            }
            catch (Exception ex)
            {
                throw new Exception("Error processing GetDataFields:" + ex.Message);
            }
        }

        public List<DTO_SCT_UserActivityLog> GetUserActivityLogs()
        {

            try
            {
                DataTable sections = SqlHelper.ExecuteDataset(ConnectionString, "SCT_GetUserActivityLogs").Tables[0];

                List<DTO_SCT_UserActivityLog> userActivityLogArray = new List<DTO_SCT_UserActivityLog>();
                for (int i = 0; i < sections.Rows.Count; i++)
                {
                   
                    userActivityLogArray.Add(AdminDataTranforms.TranformUserActivityLogToDTO(sections.Rows[i]));
                }
                return userActivityLogArray;
            }
            catch (Exception ex)
            {
                throw new Exception("Error processing GetUserActivityLogs:" + ex.Message);
            }
        }

        public List<DTO_SCT_FieldAllowedValue> GetFieldAllowedValues()
        {

            try
            {
                DataTable sections = SqlHelper.ExecuteDataset(ConnectionString, "SCT_GetFieldAllowedValues").Tables[0];

                List<DTO_SCT_FieldAllowedValue> fieldsAllowedValuesArray = new List<DTO_SCT_FieldAllowedValue>();
                for (int i = 0; i < sections.Rows.Count; i++)
                {
                   

                    fieldsAllowedValuesArray.Add(AdminDataTranforms.TranformFieldAllowedValueToDTO(sections.Rows[i]));
                }
                return fieldsAllowedValuesArray;
            }
            catch (Exception ex)
            {
                throw new Exception("Error processing GetFieldAllowedValues:" + ex.Message);
            }
        }

        public List<DTO_SCT_FormSectionType> GetFormSectionTypes()
        {

            try
            {
                DataTable sections = SqlHelper.ExecuteDataset(ConnectionString, "SCT_GetAllFormSectionTypes").Tables[0];

                List<DTO_SCT_FormSectionType> fieldsFormSectionTypesArray = new List<DTO_SCT_FormSectionType>();
                for (int i = 0; i < sections.Rows.Count; i++)
                {


                    fieldsFormSectionTypesArray.Add(AdminDataTranforms.TranformFormSectionTypeToDTO(sections.Rows[i]));
                }
                return fieldsFormSectionTypesArray;
            }
            catch (Exception ex)
            {
                throw new Exception("Error processing GetFormSectionTypes:" + ex.Message);
            }
        }
        #endregion;

        #region DTO tranform

        public int SaveForm(DTO_SCT_Form form)
        {

            try
            {
                // First we need to find out whether the row exists in the database:
                int resultInt = 0;
                DataTable forms = SqlHelper.ExecuteDataset(ConnectionString, "SCT_GetFormById", form.SCT_FormID).Tables[0];
                if (forms.Rows.Count > 1)
                {
                    throw new Exception("Error processing SaveForm: There are more Forms record for one PK=" + form.SCT_FormID);
                }
                if (forms.Rows.Count == 1)
                {
                    // UPDATE
                   resultInt = SqlHelper.ExecuteNonQueryTypedParams(ConnectionString, "SCT_UpdateForm", AdminDataTranforms.TransformFormToDataRow(form, forms));
                }
                else
                {
                    //INSERT 
                    DataTable forReturn = SqlHelper.ExecuteDatasetTypedParams(ConnectionString, "SCT_AddForm", AdminDataTranforms.TransformFormToDataRow(form, forms)).Tables[0];
                    if (forReturn.Rows.Count > 0 && !string.IsNullOrEmpty( forReturn.Rows[0]["SCT_FormId"].ToString()))
                    {
                        resultInt = int.Parse(forReturn.Rows[0]["SCT_FormId"].ToString());
                    }

                }

                return resultInt;
            }
            catch (Exception ex)
            {
                throw new Exception("Error processing SaveForm:" + ex.Message);
            }
        }

        public int SavePage(DTO_SCT_FormPage form)
        {
            try
            {
                // First we need to find out whether the row exists in the database:
                int resultInt = 0;
                DataTable forms = SqlHelper.ExecuteDataset(ConnectionString, "SCT_GetPageById", form.SCT_FormPageID).Tables[0];
                if (forms.Rows.Count > 1)
                {
                    throw new Exception("Error processing SavePage: There are more Forms record for one PK=" + form.SCT_FormPageID);
                }
                if (forms.Rows.Count == 1)
                {
                    // UPDATE
                    resultInt = SqlHelper.ExecuteNonQueryTypedParams(ConnectionString, "SCT_UpdateFormPage", AdminDataTranforms.TransformFormPageToDataRow(form, forms));
                }
                else
                {
                    //INSERT 
                    DataTable forReturn = SqlHelper.ExecuteDatasetTypedParams(ConnectionString, "SCT_AddFormPage", AdminDataTranforms.TransformFormPageToDataRow(form, forms)).Tables[0];
                    if (forReturn.Rows.Count > 0 && !string.IsNullOrEmpty(forReturn.Rows[0]["SCT_FormPageID"].ToString()))
                    {
                        resultInt = int.Parse(forReturn.Rows[0]["SCT_FormPageID"].ToString());
                    }
                }

                return resultInt;
            }
            catch (Exception ex)
            {
                throw new Exception("Error processing SavePage:" + ex.Message);
            }
        }
        public int SaveFormSection(DTO_SCT_FormSection item)
        {

            try
            {
                // First we need to find out whether the row exists in the database:
                int resultInt = 0;
                DataTable forms = SqlHelper.ExecuteDataset(ConnectionString, "SCT_GetFormSectionById", item.SCT_FormSectionID).Tables[0];
                if (forms.Rows.Count > 1)
                {
                    throw new Exception("Error processing SaveFormSection: There are more FormSection record for one PK=" + item.SCT_FormSectionID);
                }
                if (forms.Rows.Count == 1)
                {
                    // UPDATE
                    resultInt = SqlHelper.ExecuteNonQueryTypedParams(ConnectionString, "SCT_UpdateFormSection", AdminDataTranforms.TransformFormSectionToDataRow(item, forms));
                }
                else
                {
                    //INSERT 
                    DataTable forReturn = SqlHelper.ExecuteDatasetTypedParams(ConnectionString, "SCT_AddFormSection", AdminDataTranforms.TransformFormSectionToDataRow(item, forms)).Tables[0];
                    if (forReturn.Rows.Count > 0 && !string.IsNullOrEmpty(forReturn.Rows[0]["SCT_FormSectionID"].ToString()))
                    {
                        resultInt = int.Parse(forReturn.Rows[0]["SCT_FormSectionID"].ToString());
                    }

                }

                return resultInt;
            }
            catch (Exception ex)
            {
                throw new Exception("Error processing SaveFormSection:" + ex.Message);
            }
        }
        public int SaveFormSectionField(DTO_SCT_FormSectionField item)
        {

            try
            {
                // First we need to find out whether the row exists in the database:
                int resultInt = 0;
                DataTable forms = SqlHelper.ExecuteDataset(ConnectionString, "SCT_GetFormSectionFieldById", item.SCT_FormSectionFieldID).Tables[0];
                if (forms.Rows.Count > 1)
                {
                    throw new Exception("Error processing SaveFormSectionField: There are more FormSectionFields record for one PK=" + item.SCT_FormSectionFieldID);
                }
                if (forms.Rows.Count == 1)
                {
                    // UPDATE
                    resultInt = SqlHelper.ExecuteNonQueryTypedParams(ConnectionString, "SCT_UpdateFormSectionField", AdminDataTranforms.TransformFormSectionFieldToDataRow(item, forms));
                }
                else
                {
                    //INSERT 
                    DataTable forReturn = SqlHelper.ExecuteDatasetTypedParams(ConnectionString, "SCT_AddFormSectionField", AdminDataTranforms.TransformFormSectionFieldToDataRow(item, forms)).Tables[0];
                    if (forReturn.Rows.Count > 0 && !string.IsNullOrEmpty(forReturn.Rows[0]["SCT_FormSectionFieldID"].ToString()))
                    {
                        resultInt = int.Parse(forReturn.Rows[0]["SCT_FormSectionID"].ToString());
                    }

                }

                return resultInt;
            }
            catch (Exception ex)
            {
                throw new Exception("Error processing SaveFormSectionField:" + ex.Message);
            }
        }
        public int SaveDataField(DTO_SCT_DataField item)
        {

            try
            {
                // First we need to find out whether the row exists in the database:
                int resultInt = 0;
                DataTable forms = SqlHelper.ExecuteDataset(ConnectionString, "SCT_GetDataFieldById", item.SCT_DataFieldID).Tables[0];
                if (forms.Rows.Count > 1)
                {
                    throw new Exception("Error processing SaveDataField: There are more DataFields record for one PK=" + item.SCT_DataFieldID);
                }
                if (forms.Rows.Count == 1)
                {
                    // UPDATE
                    resultInt = SqlHelper.ExecuteNonQueryTypedParams(ConnectionString, "SCT_UpdateDataField", AdminDataTranforms.TransformDataFieldToDataRow(item, forms));
                }
                else
                {
                    //INSERT 
                    DataTable forReturn = SqlHelper.ExecuteDatasetTypedParams(ConnectionString, "SCT_AddDataField", AdminDataTranforms.TransformDataFieldToDataRow(item, forms)).Tables[0];
                    if (forReturn.Rows.Count > 0 && !string.IsNullOrEmpty(forReturn.Rows[0]["SCT_DataFieldID"].ToString()))
                    {
                        resultInt = int.Parse(forReturn.Rows[0]["SCT_DataFieldID"].ToString());
                    }

                }

                return resultInt;
            }
            catch (Exception ex)
            {
                throw new Exception("Error processing DataField:" + ex.Message);
            }
        }
        public int SaveDataCategory(DTO_SCT_DataCategory item)
        {

            try
            {
                // First we need to find out whether the row exists in the database:
                int resultInt = 0;
                DataTable forms = SqlHelper.ExecuteDataset(ConnectionString, "SCT_GetDataCategoryById", item.SCT_DataCategoryID).Tables[0];
                if (forms.Rows.Count > 1)
                {
                    throw new Exception("Error processing SaveDataCategory: There are more DataCategory record for one PK=" + item.SCT_DataCategoryID);
                }
                if (forms.Rows.Count == 1)
                {
                    // UPDATE
                    resultInt = SqlHelper.ExecuteNonQueryTypedParams(ConnectionString, "SCT_UpdateDataCategory", AdminDataTranforms.TransformDataCategoryToDataRow(item, forms));
                }
                else
                {
                    //INSERT 
                    DataTable forReturn = SqlHelper.ExecuteDatasetTypedParams(ConnectionString, "SCT_AddDataCategory", AdminDataTranforms.TransformDataCategoryToDataRow(item, forms)).Tables[0];
                    if (forReturn.Rows.Count > 0 && !string.IsNullOrEmpty(forReturn.Rows[0]["SCT_DataCategoryID"].ToString()))
                    {
                        resultInt = int.Parse(forReturn.Rows[0]["SCT_DataCategoryID"].ToString());
                    }

                }

                return resultInt;
            }
            catch (Exception ex)
            {
                throw new Exception("Error processing SaveDataCategory:" + ex.Message);
            }
        }
        public int SaveFormType(DTO_SCT_FormType item)
        {

            try
            {
                // First we need to find out whether the row exists in the database:
                int resultInt = 0;
                DataTable forms = SqlHelper.ExecuteDataset(ConnectionString, "SCT_GetFormTypeById", item.SCT_FormTypeID).Tables[0];
                if (forms.Rows.Count > 1)
                {
                    throw new Exception("Error processing SaveFormType: There are more FormType record for one PK=" + item.SCT_FormTypeID);
                }
                if (forms.Rows.Count == 1)
                {
                    // UPDATE
                    resultInt = SqlHelper.ExecuteNonQueryTypedParams(ConnectionString, "SCT_UpdateFormType", AdminDataTranforms.TransformFormTypeToDataRow(item, forms));
                }
                else
                {
                    //INSERT 
                    DataTable forReturn = SqlHelper.ExecuteDatasetTypedParams(ConnectionString, "SCT_AddFormType", AdminDataTranforms.TransformFormTypeToDataRow(item, forms)).Tables[0];
                    if (forReturn.Rows.Count > 0 && !string.IsNullOrEmpty(forReturn.Rows[0]["SCT_FormTypeID"].ToString()))
                    {
                        resultInt = int.Parse(forReturn.Rows[0]["SCT_FormTypeID"].ToString());
                    }

                }

                return resultInt;
            }
            catch (Exception ex)
            {
                throw new Exception("Error processing SaveFormType:" + ex.Message);
            }
        }
         
        #endregion;
    }
}